package com.googlecode.bip.dao.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.dao.UserValueDAO;
import com.googlecode.bip.dao.utils.DBHelper;
import com.googlecode.bip.entity.IUserValue;
import cx.ath.choisnet.sql.ConnectionQuery;

/**
 *
 * @author Claude CHOISNET
 */
public class MySQLUserValueDAO extends UserValueDAO
{
    private static final long serialVersionUID = 1L;
    private static final Logger logger = Logger.getLogger( MySQLUserValueDAO.class );

    @Override //Listable<IUserValue>
    public List<IUserValue> getList( String sqlQuery ) throws SQLException, NamingException
    {
        Connection connection = MySQLDAOFactory.getConnection();

        try {
            //return DBHelper.getList( this, getDataSource(), sqlQuery );
            return DBHelper.getList( this, connection, sqlQuery );
            }
        finally {
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    /**
    *
    * @param idquestion
    * @return
    * @throws SQLException
    * @throws NamingException
    * @Override
   public List<IUserValue> findUserValue( int idquestion )
       throws SQLException, NamingException
   {
       final String sql = "SELECT * FROM `questionsview` WHERE `idquestion`="
           + idquestion
           + ";";

       return getList( sql );
   }
    */

    /**
     *
     * @param iduser
     * @param idquestion
     * @param userdate
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    @Override
    public IUserValue findUserValue(
           int          iduser,
           int          idquestion,
           Timestamp    userdate
           )
       throws NamingException, SQLException
   {
       final String sql = "SELECT * FROM `uservaluesview` WHERE `iduser`="
           + iduser
           + " AND `idquestion`="
           + idquestion
           + " AND `userdate`='"
           + userdate
           +"';";

       if( logger.isDebugEnabled() ) {
           logger.debug( "findUserValue: " + sql );
           }

       Connection connection = MySQLDAOFactory.getConnection();
       final ConnectionQuery query = new ConnectionQuery( connection );

       try {
           ResultSet   rSet  = query.executeQuery( sql );
           boolean     found = rSet.first();

           if( found ) {
               return newInstance( rSet );
               }
           }
       finally {
           query.quietClose();
           MySQLDAOFactory.returnConnectionToPool( connection );
           }

       return null;
   }

   /**
    *
    * @param uservalue
    * @return
    * @throws NamingException
    * @throws SQLException
    */
    @Override
   public boolean insertUserValue( final IUserValue uservalue )
       throws NamingException, SQLException
   {
       final String sql = "CALL add_uservalue( "
           + uservalue.getIdUser()
           + ", "
           + uservalue.getIdQuestion()
           + ", '"
           + uservalue.getUserDate()
           + "', "
           + uservalue.getUserValue()
           + ", '"
           + uservalue.getUserComment()
           + "' );";

       if( logger.isDebugEnabled() ) {
           logger.debug( "insertQuestion: " + sql );
           }

       Connection connection = MySQLDAOFactory.getConnection();
       final ConnectionQuery query = new ConnectionQuery( connection );

       try {
           query.doUpdate( sql );

           return true;
           }
       finally {
           query.quietClose();
           MySQLDAOFactory.returnConnectionToPool( connection );
           }
   }

   /**
    *
    * @param iduser
    * @param idquestion
    * @param userdate
    * @return
    * @throws NamingException
    * @throws SQLException
    */
    @Override
   public boolean deleteUserValue(
       final int        iduser,
       final int        idquestion,
       final Timestamp  userdate
       )
       throws NamingException, SQLException
   {
       final String whereClause =  " WHERE `iduser`="
           + iduser
           + " AND `idquestion`="
           + idquestion
           + " AND `userdate`='"
           + userdate
           + "';";
       final String sql1 = "DELETE FROM `uservaluecomments`" + whereClause;
       final String sql2 = "DELETE FROM `uservalues`" + whereClause;

       if( logger.isDebugEnabled() ) {
           logger.debug( "deleteUserValue: sql1 = " + sql1 );
           logger.debug( "deleteUserValue: sql2 = " + sql2 );
           }

       Connection connection = MySQLDAOFactory.getConnection();
       final ConnectionQuery query = new ConnectionQuery( connection );

       try {
           // TODO: improve (roll back -> move db)
           /*int x1 =*/ query.doUpdate( sql1 );
           int x2 = query.doUpdate( sql2 );

           if( /*x1 == 1 &*/ x2 == 1 ) {
               return true;
               }
           }
       finally {
           query.quietClose();
           MySQLDAOFactory.returnConnectionToPool( connection );
           }

       return false;
   }

   /**
    *
    * @param uservalue
    * @return
    * @throws NamingException
    * @throws SQLException
    */
    @Override
   public boolean updateUserValue( final IUserValue uservalue )
       throws NamingException, SQLException
   {
       final String sql = "CALL add_or_update_uservalue( "
           + uservalue.getIdUser()
           + ", "
           + uservalue.getIdQuestion()
           + ", '"
           + uservalue.getUserDate()
           + "', "
           + uservalue.getUserValue()
           + ", '"
           + uservalue.getUserComment()
           + "' );";

       if( logger.isDebugEnabled() ) {
           logger.debug( "updateUserValue: " + sql );
           }

       Connection connection = MySQLDAOFactory.getConnection();
       final ConnectionQuery query = new ConnectionQuery( connection );

       try {
           query.doUpdate( sql );
           }
       finally {
           query.quietClose();
           MySQLDAOFactory.returnConnectionToPool( connection );
           }

       return true;
   }

}
